/*********************************************************************************
 *      Copyright:  (C) 2021 Simply myself<1239726156@qq.com>
 *                  All rights reserved.
 *
 *       Filename:  my_sqlite.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(2021年08月10日)
 *         Author:  Simply myself <1239726156@qq.com>
 *      ChangeLog:  1, Release initial version on "2021年08月10日 16时08分43秒"
 *                 
 ********************************************************************************/

#include "my_sqlite.h"

int sqlite_create_table (sqlite3 *db, char *table_name)
{
	int   rv = 0;
	char  sql[256];
	char  *errmsg = 0;
	char  exit_msg[50];
	
	if ( !db || !table_name )
	{
		printf ("[ERROR]: Invalid parameter in %s\n\n", __FUNCTION__);
		return -1;
	}
	
	memset (sql, 0, sizeof(sql));
	//snprintf (sql, sizeof(sql), "CREATE TABLE %s(ID  INT  PRIMARY KEY  NOT NULL, TEMPERATURE	FLOAT	NOT NULL, TIMESTAMP      TEXT      NOT NULL)", table_name);
	snprintf (sql, sizeof(sql), "CREATE TABLE %s(ID INT PRIMARY KEY     NOT NULL, \
	                                             TEMPERATURE    FLOAT   NOT NULL, \
												 TIMESTAMP      TEXT    NOT NULL);", table_name);
		  
	rv = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if ( rv != SQLITE_OK )
	{
		memset (exit_msg, 0, sizeof(exit_msg));
		snprintf (exit_msg, sizeof(exit_msg), "table %s already exists", table_name);
		if ( strncmp(errmsg, exit_msg, strlen(errmsg)) == 0 )
		{
			printf ("%s\n", errmsg);
		}
		else
		{
			printf ("[ERROR]: SQL error: %s\n", errmsg);
			rv = -2;
		}
		sqlite3_free(errmsg);
	}
	
	return rv;
}


static int get_id_callback(void *data, int argc, char **argv, char **azColName)
{
	int *found = (int *)data;
	
	printf ("get_max_id_callback:\n");
	
	int i;
	for(i=0; i<argc; i++){
		printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
		if ( !argv[i] )
		{
			printf("");
			*found = 1;
		}
		
	}
	
	return 0;
}


int get_id(struct database_s *DB)
{
	static int last_id = -1;
	int        id;
	int        found = 0;
	char       sql[256];
	char      *errmsg;
	int        rv = 0;
	
	if ( !DB )
	{
		printf ("[ERROR]: Invalid parameter in %s\n\n", __FUNCTION__);
		return -1;
	}
	
	/* 获取min、max、msgnum */
	rv = get_max_id(DB);
	if ( rv < 0 )
	{
		printf ("get max_id failure: rv[%d]\n\n", rv);
		return -2;
	}
	printf ("get max_id success: max_id[%d]\n\n", DB->max_id);
	
	rv = get_min_id(DB);
	if ( rv < 0 )
	{
		printf ("get min_id failure: rv[%d]\n\n", rv);
		return -3;
	}
	printf ("get min_id success: min_id[%d]\n\n", DB->min_id);
	
	rv = get_msg_num (DB);
	if ( rv < 0 )
	{
		printf ("get msg_num failure: rv[%d]\n\n", rv);
		return -4;
	}
	printf ("get msg_num success: msg_num[%d]\n\n", DB->msg_num);
	
	
	if ( DB->max_id == -1 || DB->min_id == -1 ) //为空
	{
		DB->full_flag = 0;
		DB->id = 0;
	}
	else if ( (DB->max_id - DB->min_id) == DB->msg_num ) //说明已满 xxxxxxxxxx
	{
		DB->full_flag = 1;
		DB->id = last_id + 1;
		last_id = DB->id;
	}
	else if ( (DB->max_id==DB->min_id) || ((DB->max_id - DB->min_id) == (DB->msg_num - 1)) ) //说明没满，并且是连续存储的 xx____、__xx__、____xx
	{
		DB->full_flag = 0;
		if ( DB->max_id == DB->max_size )  //第三种情况
		{
			DB->id = 1;
		}
		else  //其余两种情况
		{
			DB->id = DB->max_id + 1;
		}
	}
	else if ( DB->min_id==0 && DB->max_id==DB->max_size && (DB->max_id-DB->min_id != DB->msg_num) ) //说明没满，且中间的id有空位 
	{
		DB->full_flag = 0;
		id = 0;
		while ( !found )
		{
			memset (sql, 0, sizeof(sql));
			snprintf (sql, sizeof(sql), "SELECT * FROM %s WHERE ID = %d;", DB->table_name, id);
			rv = sqlite3_exec (DB->db, sql, get_id_callback, (void *)&found, &errmsg );
			if ( rv != SQLITE_OK )
			{
				printf ("get_id failure: %s\n", errmsg);
				sqlite3_free(errmsg);
				return -5;
			}
			id++;
		}
		id--;
		DB->id = id;
	}
	
	return 0;
	
}


static int get_max_id_callback(void *data, int argc, char **argv, char **azColName)
{
	int i;
	int *max_id = (int *)data;
	char *value;
	
	printf ("get_max_id_callback:\n");
	
	for(i=0; i<argc; i++){
		printf("%s = %s\t", azColName[i], argv[i] ? argv[i] : "NULL");
		//printf ("argv[%d]=%s\n", argc, argv[i]);
		if ( !argv[i] )  //表为空的情况
		{
			*max_id = -1;
			printf ("Table is NULL\n");
			printf ("Set %s = %d\n", azColName[i], *max_id);
			//printf("Set %s = %s\n", azColName[i], argv[i] ? argv[i] : "-1");
		}
		else             //表不为空
		{
			*max_id = atoi(argv[i]);
			printf ("Table is not NULL\n");
			printf ("%s = %d\n", azColName[i], *max_id);
		}
	}
	
	return 0;
}


int get_max_id (struct database_s *DB)
{
	int        rv = 0;
	char       sql[256];
	char      *errmsg;
	
	if ( !DB )
	{
		printf ("[ERROR]: Invalid parameter in %s\n\n", __FUNCTION__);
		return -1;
	}
	
	memset (sql, 0, sizeof(sql));
	snprintf (sql, sizeof(sql), "SELECT max(ID) FROM %s;", DB->table_name);
	rv = sqlite3_exec (DB->db, sql, get_max_id_callback, (void *)&DB->max_id, &errmsg);
	//printf ("------ debug ------\n");
	if (rv != SQLITE_OK )
	{
		printf ("get max_id failure: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -2;
	}
	else 
	{
		printf ("get_max_id success\n");
	}
	
	return 0;
	
}


static int get_min_id_callback(void *data, int argc, char **argv, char **azColName)
{
	int i;
	int *min_id = (int *)data;
	
	printf ("get_max_id_callback:\n");
	
	for(i=0; i<argc; i++){
		printf("%s = %s\t", azColName[i], argv[i] ? argv[i] : "NULL");
		
		if ( !argv[i] )  //表为空的情况
		{
			*min_id = -1;
			printf ("Table is NULL\n");
			printf ("Set %s = %d\n", azColName[i], *min_id);
			//printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "-1");
		}
		else             //表不为空
		{
			*min_id = atoi(argv[i]);
			printf ("Table is not NULL\n");
			printf ("%s = %d\n", azColName[i], *min_id);
		}
	}
	
	return 0;
}


int get_min_id (struct database_s *DB)
{
	int        rv = 0;
	char       sql[256];
	char      *errmsg;
	
	if ( !DB )
	{
		printf ("[ERROR]: Invalid parameter in %s\n\n", __FUNCTION__);
		return -1;
	}
	
	memset (sql, 0, sizeof(sql));
	snprintf (sql, sizeof(sql), "SELECT min(ID) FROM %s;", DB->table_name);
	rv = sqlite3_exec (DB->db, sql, get_min_id_callback, (void *)&DB->min_id, &errmsg);
	if (rv != SQLITE_OK )
	{
		printf ("get min_id failure: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -2;
	}
	else 
	{
		printf ("get min_id success\n");
	}
	
	return 0;
}


static int get_msg_num_callback(void *data, int argc, char **argv, char **azColName)
{
	int i;
	int *msg_num = (int *)data;
	
	printf ("get_max_id_callback:\n");
	
	for(i=0; i<argc; i++){
		printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
		*msg_num = atoi(argv[i]);
	}
	
	return 0;
}


int get_msg_num (struct database_s *DB)
{
	int        rv = 0;
	char       sql[256];
	char      *errmsg;
	
	if ( !DB )
	{
		printf ("[ERROR]: Invalid parameter in %s\n\n", __FUNCTION__);
		return -1;
	}
	
	memset (sql, 0, sizeof(sql));
	snprintf (sql, sizeof(sql), "SELECT count(ID) FROM %s;", DB->table_name);
	rv = sqlite3_exec (DB->db, sql, get_msg_num_callback, (void *)&DB->msg_num, &errmsg);
	if (rv != SQLITE_OK )
	{
		printf ("get msg_num failure: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -2;
	}
	else 
	{
		printf ("get msg_num success\n");
	}
	
	return 0;
}


char *get_cur_time (void)
{
	time_t     tm;
	struct tm *tmp;
	char       tim[32];
	char      *string = NULL;
	
	memset(tim, 0, sizeof(tim));
	if ( time(&tm) == -1)
	{
		printf ("[ERROR]: get time failure: %s\n", strerror(errno));
	}
	
	tmp = gmtime(&tm);
	snprintf(tim, sizeof(tim), "%04d-%02d-%02d %02d:%02d:%02d", tmp->tm_year+1900, tmp->tm_mon+1, tmp->tm_mday, (tmp->tm_hour+8)%24, tmp->tm_min, tmp->tm_sec);
	
	//memcpy(string, (char*)tim, strlen((char*)tim)+1);
	string = tim;
	
	return string;	
}


int insert_data (sqlite3 *db, char *table_name, int *id, float *temp, char *timestamp)
{
	char  sql[256];
	char *errmsg;
	
	if ( !db || !table_name || !id || !temp || !timestamp )
	{
		printf ("[ERROR]: Invalid parameter in %s\n\n", __FUNCTION__);
		return -1;
	}
	
	memset (sql, 0, sizeof(sql));
	snprintf (sql, sizeof(sql), "INSERT into %s(ID, TEMPERATURE, TIMESTAMP) VALUES(%d, %.2f, '%s');",table_name, *id, *temp, timestamp);
	
	if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf ("insert data failure: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -2;
	}
	printf ("insert data success\n");
	
	return 0;
	
}


int update_data (sqlite3 *db, char *table_name, int *id, float *temp, char *timestamp)
{
	char  sql[256];
	char *errmsg;
	
	if ( !db || !table_name || !id || !temp || !timestamp )
	{
		printf ("[ERROR]: Invalid parameter in %s\n\n", __FUNCTION__);
		return -1;
	}
	
	memset (sql, 0, sizeof(sql));
	snprintf (sql, sizeof(sql), "UPDATE %s set TEMPERATURE = %.2f WHERE ID = %d;" \
	                            "UPDATE %s set TIMESTAMP = %s WHERE ID = %d;", table_name, *temp, *id, table_name, timestamp, *id);
	
	if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf ("update data failure: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -2;
	}
	printf ("update data success\n");
	
	return 0;
	
}


int delete_data (sqlite3 *db, char *table_name, int *id)
{
	char  sql[256];
	char *errmsg;
	
	if ( !db || !table_name || !id )
	{
		printf ("[ERROR]: Invalid parameter in %s\n\n", __FUNCTION__);
		return -1;
	}
	
	memset (sql, 0, sizeof(sql));
	snprintf (sql, sizeof(sql), "DELETE FROM %s where ID = %d;", table_name, *id);
	
	if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf ("delete data failure: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -2;
	}
	printf ("delete data success\n");
	
	return 0;
}









